package com.example.framework.excel.excelUtils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.example.framework.excel.annotation.ExcelExport;
import com.example.framework.excel.annotation.ExcelImport;
import com.example.framework.excel.annotation.ExcelSheet;
import com.example.framework.excel.enums.BasicCode;
import com.example.framework.excel.enums.ExcelType;
import com.example.framework.exception.ExcelException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 * excel 工具类
 * </p>
 *
 * @author gx
 * @date 2021/6/11 9:49
 */

@Slf4j
public class ExcelUtil {
    /**
     * xls 后缀
     */
    public static final String XLS = "xls";
    /**
     * xlsx 后缀
     */
    public static final String XLS_X = "xlsx";
    /**
     * 列不对等
     */
    public static final String ROW_NUM_ERROR = "导入模板异常！";

    /**
     * 文件不存在
     */
    public static final String FILE_NOT_ERROR = "文件不存在！";
    /**
     * 表头错误
     */
    public static final String NAME_ERROR = "表头错误！";
    /**
     * 实体空异常
     */
    public static final String BEAN_ERROR = "实体空异常！";
    /**
     * 科学计数
     */
    public static final String E = "e";


    /**
     * 传入文本对象输出list集合（导入）
     *
     * @param file  流文件
     * @param clazz 要转义成的类对象
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz) {
        // 检查文件
        Workbook workbook = getWorkBook(file);
        List<T> list = new ArrayList<T>();
        checkFile(file);
        // 获得HSSFWorkbook工作薄对象
        //获取对象总数量并按注解排序
        Field[] fields = getSortFieldsImport(clazz);

        if (workbook != null) {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                // 获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null || sheet.getLastRowNum() == 0) {
                    continue;
                }
                // 获取当前sheet工作表的列总数
                int firstLine = sheet.getRow(0).getPhysicalNumberOfCells();
                if (fields.length != firstLine) {
                    throw new ExcelException(BasicCode.IMPORT_TEMPLATE_ERR);
                }
                // 获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                // 获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                // 循环所有行
                for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                    // 获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    Object obj = getNewInstance(clazz);
                    for (int cellNum = 0; cellNum < firstLine; cellNum++) {
                        // 取出对应注解
                        ExcelImport excelImport = fields[cellNum].getAnnotation(ExcelImport.class);
                        Cell cell = row.getCell(cellNum);
                        if (rowNum == 0) {
                            // 第一行 判断表头名称
                            if (cell == null || StringUtils.isEmpty(cell.getStringCellValue())
                                    || !cell.getStringCellValue().equals(excelImport.titleName())) {
                                throw new ExcelException(BasicCode.NAME_ERR);
                            }
                            continue;
                        }
                        Object value = getCellValue(cell);
                        // 判断注解是否允许空值
                        if (!excelImport.empty()) {
                            if (value == null || "".equals(value)) {
                                log.error("【excel导入】{} 列不能为空！", excelImport.titleName());
                                throw new ExcelException(10006, excelImport.titleName() + "列不能为空");
                            }
                        }
                        // 根绝类型 实体类赋值
                        createBean(fields[cellNum], obj, value);
                    }
                    if (rowNum == 0) {
                        // 表头不做记录
                        continue;
                    }
                    list.add((T) obj);
                }
            }
        }
        return list;
    }

    /**
     * 传入文本对象输出Class（导入）
     *
     * @param file  流文件
     * @param clazz 要转义成的类对象
     * @return
     */
    public static <T> T importMultiple(MultipartFile file, Class<T> clazz) {
        // 检查文件
        checkFile(file);
        // 获得HSSFWorkbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //获取对象总数量
        Field[] fields = getSortFieldsByExcelSheet(clazz);
        Object obj = getNewInstance(clazz);
        if (workbook != null) {
            ExcelSheet excel = null;
            Field field = null;
            int order = 0;
            Sheet sheet = null;
            for (int fieldNum = 0; fieldNum < fields.length; fieldNum++) {
                field = fields[fieldNum];
                excel = field.getAnnotation(ExcelSheet.class);
                order = excel.sheetNum();
                sheet = workbook.getSheetAt(order);
                Type genericType = field.getGenericType();
                ParameterizedType pt = (ParameterizedType) genericType;
                //得到泛型里的class类型对象
                Class<?> genericClazz = (Class<?>) pt.getActualTypeArguments()[0];
                createBean(field, obj, importMultiple(sheet, genericClazz, excel));

            }

        }

        return (T) obj;
    }

    private static <T> List<T> importMultiple(Sheet sheet, Class<T> clazz, ExcelSheet excelSheet) {
        int titleRow = excelSheet.titleRow();
        Field[] fields = getSortFieldsImport(clazz);
        List<T> list = new ArrayList<T>();
        // 获取当前sheet工作表的列总数
        int firstLine = sheet.getRow(titleRow).getPhysicalNumberOfCells();
        if (fields.length != firstLine) {
            throw new ExcelException(BasicCode.IMPORT_TEMPLATE_ERR);
        }
        // 获得当前sheet的开始行
        int firstRowNum = excelSheet.row();
        // 获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        // 验证表头名称
        Row row = null;
        // 循环所有行
        for (int rowNum = titleRow; rowNum <= lastRowNum; rowNum++) {
            // 获得当前行
            row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            Object obj = getNewInstance(clazz);

            for (int fieldNum = 0; fieldNum < fields.length; fieldNum++) {
                ExcelImport excel = fields[fieldNum].getAnnotation(ExcelImport.class);
                Cell cell = row.getCell(excel.column());
                if (rowNum == titleRow) {
                    // 第一行 判断表头名称
                    if (cell == null || StringUtils.isEmpty(cell.getStringCellValue())
                            || !cell.getStringCellValue().equals(excel.titleName())) {
                        log.info("【excel导入】-{}-{}", excelSheet.sheetName(), excel.titleName() + NAME_ERROR);
                        throw new ExcelException(10005,
                                "【" + excelSheet.sheetName() + "】【" + excel.titleName() + "】" + NAME_ERROR);
                    }
                    continue;
                }
                Object value = getCellValue(cell);
                // 判断注解是否允许空值
                if (!excel.empty()) {
                    if (value == null || "".equals(value)) {
                        log.error("【excel导入】-{}-{}列不能为空！", excelSheet.sheetName(), excel.titleName());
                        throw new ExcelException(10006,
                                "【" + excelSheet.sheetName() + "】【" + excel.titleName() + "】" + "列不能为空！");
                    }
                }
                // 根绝类型 实体类赋值
                createBean(fields[fieldNum], obj, value);

            }

            if (rowNum == titleRow) {
                // 表头不做记录
                rowNum = firstRowNum - 1;
                continue;
            }
            list.add((T) obj);
        }
        if (list.size() == 0 && !excelSheet.empty()) {
            log.error("【excel导入】{} 页签不能为空！", excelSheet.sheetName());
            throw new ExcelException(10011, excelSheet.sheetName() + "页签不能为空！");
        }
        return list;
    }


    /**
     * 导出模版
     *
     * @param excelName excel 名称
     * @param clazz     数据集
     * @param response  使用response可以导出到浏览器
     * @param <T>
     * @return
     */
    public static <T> Boolean exportTemplate(String excelName, Class<T> clazz, HttpServletResponse response) {
        return export(excelName, null, clazz, ExcelType.XLS, response, false);
    }

    /**
     * 导出模版
     *
     * @param excelName excel 名称
     * @param clazz     数据集
     * @param type      excel 类型
     * @param response  使用response可以导出到浏览器
     * @param <T>
     * @return
     */
    public static <T> Boolean exportTemplate(String excelName,
                                             Class<T> clazz,
                                             ExcelType type,
                                             HttpServletResponse response) {
        return export(excelName, null, clazz, type, response, false);
    }

    /**
     * excel 导出 （对象）
     *
     * @param excelName excel 名称
     * @param list      数据集
     * @param clazz     反射clazz
     * @param response  使用response可以导出到浏览器
     * @param <T>
     * @return
     */
    public static <T> Boolean exportExcel(String excelName,
                                          List<T> list,
                                          Class<T> clazz,
                                          HttpServletResponse response) {
        return export(excelName, list, clazz, ExcelType.XLS, response, true);
    }

    /**
     * excel 导出 （对象）
     *
     * @param excelName excel 名称
     * @param list      数据集
     * @param clazz     反射clazz
     * @param type      excel 类型
     * @param response  使用response可以导出到浏览器
     * @param <T>
     * @return
     */
    public static <T> Boolean exportExcel(String excelName,
                                          List<T> list,
                                          Class<T> clazz,
                                          ExcelType type,
                                          HttpServletResponse response) {
        return export(excelName, list, clazz, type, response, true);
    }

    /**
     * excel 导出 （Map）
     *
     * @param excelName excel 名称
     * @param clazz     反射clazz
     * @param list      数据集
     * @param response  使用response可以导出到浏览器
     * @param <T>
     * @return
     */
    public static <T> Boolean exportExcel(String excelName,
                                          Class<T> clazz,
                                          List<Map<String, Object>> list,
                                          HttpServletResponse response) {
        return exportExcel(excelName, clazz, list, ExcelType.XLS, response, true);
    }

    /**
     * excel 导出 （Map）
     *
     * @param excelName excel 名称
     * @param clazz
     * @param list      数据集
     * @param type      excel 类型
     * @param response  使用response可以导出到浏览器
     * @param <T>
     * @return
     */
    public static <T> Boolean exportExcel(String excelName,
                                          Class<T> clazz,
                                          List<Map<String, Object>> list,
                                          ExcelType type,
                                          HttpServletResponse response) {
        return exportExcel(excelName, clazz, list, type, response, false);
    }

    /**
     * excel 导出 （Map）
     *
     * @param excelName excel 名称
     * @param clazz
     * @param list      数据集
     * @param type      excel 类型
     * @param response  使用response可以导出到浏览器
     * @param flag      true：数据导出 false：模版导出
     * @param <T>
     * @return
     */
    private static <T> Boolean exportExcel(String excelName,
                                           Class<T> clazz,
                                           List<Map<String, Object>> list,
                                           ExcelType type,
                                           HttpServletResponse response,
                                           boolean flag) {
        if (list == null || list.size() == 0) {
            log.error("【excel导出】{}", "excel导出数据空异常！");
            return false;
        }
        List<T> ts = JSONArray.parseArray(JSON.toJSONString(list), clazz);
        return export(excelName, ts, clazz, type, response, flag);
    }

    /**
     * 模板导出多种数据
     *
     * @param excelName
     * @param clazz
     * @param response
     * @param <T>
     * @return
     */
    public static <T> Boolean exportMultipleTemplate(String excelName,
                                                     Class<T> clazz,
                                                     HttpServletResponse response) {

        return exportMultiple(excelName, null, clazz, ExcelType.XLS, response, false);

    }

    /**
     * 导出多种数据
     *
     * @param excelName
     * @param model
     * @param clazz
     * @param response
     * @param <T>
     * @return
     */
    public static <T> Boolean exportMultiple(String excelName,
                                             T model,
                                             Class<T> clazz,
                                             HttpServletResponse response) {

        return exportMultiple(excelName, model, clazz, ExcelType.XLS, response, true);

    }

    /**
     * 导出多种数据
     *
     * @param excelName
     * @param model
     * @param clazz
     * @param type
     * @param response
     * @param <T>
     * @return
     */
    public static <T> Boolean exportMultiple(String excelName,
                                             T model,
                                             Class<T> clazz,
                                             ExcelType type,
                                             HttpServletResponse response) {

        return exportMultiple(excelName, model, clazz, type, response, true);

    }

    /**
     * 导出多种数据
     *
     * @param excelName
     * @param model
     * @param clazz
     * @param type
     * @param response
     * @param flag
     * @param <T>
     * @return
     */
    public static <T> Boolean exportMultiple(String excelName,
                                             T model,
                                             Class<T> clazz,
                                             ExcelType type,
                                             HttpServletResponse response,
                                             boolean flag) {


        // 设置默认文件名为当前时间：年月日时分秒
        if (StringUtils.isEmpty(excelName)) {
            log.info("【excel导出】{}", "excel导出未设置文件名，默认使用时间戳代替！");
            excelName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        }
        createResponse(excelName, response, type);
        //获取对象总数量
        Field[] fields = getSortFieldsByExcelSheet(clazz);
        Workbook workbook = getWorkbook(type);
        CellStyle titleCellStyle = getTitleCellStyle(workbook);

        for (int fieldNum = 0; fieldNum < fields.length; fieldNum++) {

            Field field = fields[fieldNum];
            Type genericType = field.getGenericType();
            List<?> list = null;
            if (flag) {
                try {
                    list = (List<?>) field.get(model);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    throw new ExcelException(BasicCode.EXCEL_EXPORT_ERR);
                }
            }

            ParameterizedType pt = (ParameterizedType) genericType;
            //得到泛型里的class类型对象
            Class<?> sheetClazz = (Class<?>) pt.getActualTypeArguments()[0];
            String sheetName = field.getAnnotation(ExcelSheet.class).sheetName();
            createSheet(workbook, titleCellStyle, sheetName, list, sheetClazz, flag);

        }
        //将文件输出
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            return true;
        } catch (IOException e) {
            log.error("excel导出异常!{}", e);

            throw new ExcelException(BasicCode.EXCEL_EXPORT_ERR);
        }

    }

    /**
     * excel 导出 （对象）
     *
     * @param excelName excel 名称
     * @param list      数据集
     * @param clazz     反射clazz
     * @param type      excel 类型
     * @param response  使用response可以导出到浏览器
     * @param flag      true：数据导出 false：模版导出
     * @param <T>
     * @return
     */
    private static <T> Boolean export(String excelName,
                                      List<T> list,
                                      Class<T> clazz,
                                      ExcelType type,
                                      HttpServletResponse response,
                                      boolean flag) {
        if (flag) {
            // 非模版导出，判断数据是否为空！
            if (list == null || list.size() == 0) {
                log.error("【excel导出】{}", "excel导出数据空异常！");
                return false;
            }
        }
        // 设置默认文件名为当前时间：年月日时分秒
        if (StringUtils.isEmpty(excelName)) {
            log.info("【excel导出】{}", "excel导出未设置文件名，默认使用时间戳代替！");
            excelName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        }
        createResponse(excelName, response, type);
        Workbook workbook = getWorkbook(type);
        CellStyle titleCellStyle = getTitleCellStyle(workbook);

        createSheet(workbook, titleCellStyle, excelName, list, clazz, flag);
        try {
            //将文件输出
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            return true;
        } catch (IOException e) {
            e.printStackTrace();
            throw new ExcelException(BasicCode.EXCEL_EXPORT_ERR);
        }
    }

    /**
     * 创建excel工作簿
     *
     * @param workbook   工作簿
     * @param sheetName  sheet 名称
     * @param list       数据集
     * @param sheetClazz 反射clazz
     * @param flag       true：数据导出 false：模版导出
     */
    private static void createSheet(Workbook workbook,
                                    CellStyle titleCellStyle,
                                    String sheetName,
                                    List<?> list,
                                    Class<?> sheetClazz,
                                    boolean flag) {

        Field[] fields = getSortFieldsExcelExport(sheetClazz);

        // 创建一个工作表sheet 默认是表名是sheet0
        Sheet sheet = workbook.createSheet(sheetName);
        //设置excel 样式 （第一行格式）
        setWorkBook(workbook, titleCellStyle, sheet, fields, flag);
        // CellStyle 缓存
        Map<String, CellStyle> hashMap = new HashMap<>(8);
        try {
            if (flag) {
                // 开始生成excel
                for (int rowIndex = 1; rowIndex <= list.size(); rowIndex++) {
                    Object obj = list.get(rowIndex - 1);
                    Field[] sortFields = getSortFieldsExcelExport(obj.getClass());
                    //创建第 rowIndex 行）
                    Row row = sheet.createRow(rowIndex);
                    for (int i = 0; i < sortFields.length; i++) {
                        Field field = sortFields[i];
                        if (!field.isAccessible()) {
                            field.setAccessible(true);
                        }
                        Object object = new PropertyDescriptor(field.getName(), sheetClazz).getReadMethod().invoke(obj);

                        if (!field.getAnnotation(ExcelExport.class).empty() && object == null) {
                            log.error("【excel导出】class映射地址：{},空指针参数：{},{}",
                                    sheetClazz.getCanonicalName(), field.getName(), "数据集空指针");
                            throw new ExcelException(10010,
                                    "【excel导出】class映射地址：" + sheetClazz.getCanonicalName()
                                            + ",空指针参数：" + field.getName() + ",数据集空指针");
                        }
                        setValue(getCell(workbook, hashMap, row, i, object, field), object, field);
                    }
                }
            }

        } catch (Exception e) {
            log.error("【excel导出】sheetName:{}, sheetClazz:{}, e:{}", sheetName, sheetClazz, e);
            e.printStackTrace();
            throw new ExcelException(BasicCode.EXCEL_EXPORT_ERR);
        }

    }

//    ---------------------------------------------- excel 工具 ---------------------------------------------


    /**
     * 设置表格内容的值
     *
     * @param cell  单元格对象
     * @param value 单元格的值
     */
    private static void setValue(Cell cell, Object value, Field field) {
        if (value == null || "".equals(value)) {
            return;
        } else if (value instanceof String) {
            cell.setCellValue(value.toString());
        } else if (value instanceof Integer
                || value instanceof Double
                || value instanceof Float
                || value instanceof Long
                || value instanceof Short
                || value instanceof BigDecimal) {
            if (field.getAnnotation(ExcelExport.class).cellType().isMoney()) {
                // 判断类型
                BigDecimal bi1 = new BigDecimal(value.toString());
                int scale = field.getAnnotation(ExcelExport.class).cellType().decimalType().getScale();
                cell.setCellValue(bi1.setScale(scale, BigDecimal.ROUND_HALF_UP).toString());
            } else {
                cell.setCellValue(value.toString());
            }
        } else if (value instanceof Date) {
            SimpleDateFormat sdf = new SimpleDateFormat(
                    field.getAnnotation(ExcelExport.class).cellType().timeType().getTimeType());
            cell.setCellValue(sdf.format((Date) value));
        }
    }

    /**
     * 设置excel单元格样式
     *
     * @param workbook
     * @param hashMap
     * @param row
     * @param num
     * @param value
     * @param field
     * @return
     */
    private static Cell getCell(Workbook workbook, Map<String, CellStyle> hashMap,
                                Row row, int num,
                                Object value,
                                Field field) {
        CellStyle cellStyle;
        // 获取指定单元格
        Cell cell = row.createCell(num);
        // 设置类型
        DataFormat format = workbook.createDataFormat();
        if (value instanceof Integer
                || value instanceof Double
                || value instanceof Float
                || value instanceof Long
                || value instanceof Short
                || value instanceof BigDecimal) {
            if (field.getAnnotation(ExcelExport.class).cellType().isMoney()) {
                cellStyle = hashMap.get(
                        field.getAnnotation(ExcelExport.class).cellType().decimalType().getDecimal());
                if (cellStyle == null) {
                    cellStyle = getCellStyle(workbook);
                    cellStyle.setDataFormat(format.getFormat(
                            field.getAnnotation(ExcelExport.class).cellType().decimalType().getDecimal()));
                    hashMap.put(field.getAnnotation(ExcelExport.class).cellType().decimalType().getDecimal(),
                            cellStyle);
                }
            } else {
                cellStyle = hashMap.get("@");
                if (cellStyle == null) {
                    cellStyle = getCellStyle(workbook);
                    cellStyle.setDataFormat(format.getFormat("@"));
                    hashMap.put("@", cellStyle);
                }
            }
        } else if (value instanceof Date) {
            cellStyle = hashMap.get(field.getAnnotation(ExcelExport.class).cellType().timeType().getTimeType());
            if (cellStyle == null) {
                cellStyle = getCellStyle(workbook);
                cellStyle.setDataFormat(format.getFormat(
                        field.getAnnotation(ExcelExport.class).cellType().timeType().getTimeType()));
                hashMap.put(field.getAnnotation(ExcelExport.class).cellType().timeType().getTimeType(), cellStyle);
            }
        } else {
            cellStyle = hashMap.get("@");
            if (cellStyle == null) {
                cellStyle = getCellStyle(workbook);
                cellStyle.setDataFormat(format.getFormat("@"));
                hashMap.put("@", cellStyle);
            }
        }
        cell.setCellStyle(cellStyle);
        return cell;
    }

    /**
     * 设置excel 样式 （第一行格式）
     *
     * @param workbook
     * @param cellStyle
     * @param sheet
     * @param fields
     * @param flag      true：数据导出 false：模版导出
     */
    private static void setWorkBook(Workbook workbook, CellStyle cellStyle, Sheet sheet, Field[] fields, boolean flag) {
        //写入excel的表头（创建第一行）
        Row row = sheet.createRow(0);
        // 设置类型
        DataFormat format = workbook.createDataFormat();
        // 设置列宽、表头、数据类型
        for (int i = 0; i < fields.length; i++) {
            if (!fields[i].getAnnotation(ExcelExport.class).template() && !flag) {
                continue;
            }
            //设置宽度
            sheet.setColumnWidth(i, fields[i].getAnnotation(ExcelExport.class).titleSize() * 256);
            //创建第一行
            Cell cell = row.createCell(i);
            //设置表头名称
            cell.setCellValue(fields[i].getAnnotation(ExcelExport.class).titleName());
            cell.setCellStyle(cellStyle);
            cellStyle.setDataFormat(format.getFormat("@"));
            //sheet.setDefaultColumnStyle(i, cellStyle);

        }
    }

    /**
     * 初始化样式属性
     *
     * @param workbook
     * @return
     */
    private static CellStyle getTitleCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();

        // 设置对齐方式为居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置自动换行
        cellStyle.setWrapText(true);
        // 设置单元格内容垂直对其方式为居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        font.setBold(true);
        cellStyle.setFont(font);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }

    /**
     * 初始化样式属性
     *
     * @param workbook
     * @return
     */
    private static CellStyle getCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置对齐方式为居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置自动换行
        cellStyle.setWrapText(true);
        // 设置单元格内容垂直对其方式为居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        cellStyle.setFont(font);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        return cellStyle;
    }

    /**
     * 创建excel 导出 response信息
     *
     * @param excelName
     * @param response
     */
    private static void createResponse(String excelName, HttpServletResponse response, ExcelType type) {
        // 设置response头信息
        //        response.reset();
        // 改成输出excel文件
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
        try {
            switch (type) {
                case XLS:
                    response.setHeader("Content-disposition", "attachment; filename="
                            + new String(URLEncoder.encode(excelName, "UTF-8").getBytes("UTF-8"),
                            "ISO8859-1") + ".xls");
                    break;
                case XLS_X:
                    response.setHeader("Content-disposition", "attachment; filename="
                            + new String(URLEncoder.encode(excelName, "UTF-8").getBytes("UTF-8"),
                            "ISO8859-1") + ".xlsx");
                    break;
                default:
                    log.error("【excel导出】{}", "excel类型错误，只支持xls与xlsx！");
                    throw new ExcelException(BasicCode.XLS_XLSX_ERR);
            }
        } catch (UnsupportedEncodingException e) {
            log.error("【excel导出】{}", "设置response信息异常！");
            throw new ExcelException(BasicCode.RESPONSE_ERR);
        }
    }

    /**
     * 根据实体类型 赋值数据
     *
     * @param field
     * @param newInstance
     * @param value
     * @param <T>
     */
    private static <T> void createBean(Field field, T newInstance, Object value) {
        if (!field.isAccessible()) {
            field.setAccessible(true);
        }
        try {
            if (value == null) {
                field.set(newInstance, null);
            } else if (Long.class.equals(field.getType())) {
                field.set(newInstance, Long.valueOf(String.valueOf(value)));
            } else if (String.class.equals(field.getType())) {
                field.set(newInstance, String.valueOf(value));
            } else if (Integer.class.equals(field.getType())) {
                field.set(newInstance, Integer.valueOf(String.valueOf(value)));
            } else if (Date.class.equals(field.getType())) {
                SimpleDateFormat sdf = new SimpleDateFormat(
                        field.getAnnotation(ExcelImport.class).cellType().timeType().getTimeType());
                if (value instanceof Date) {
                    field.set(newInstance, sdf.parse(sdf.format(value)));
                } else {
                    field.set(newInstance, sdf.parse(value.toString()));
                }
            } else if (Boolean.class.equals(field.getType())) {
                field.set(newInstance, (Boolean) value);
            } else if (Double.class.equals(field.getType())) {
                field.set(newInstance, Double.valueOf(String.valueOf(value)));
            } else if (Float.class.equals(field.getType())) {
                field.set(newInstance, Float.valueOf(String.valueOf(value)));
            } else if (BigDecimal.class.equals(field.getType())) {
                field.set(newInstance, new BigDecimal(String.valueOf(value)));
            } else {
                field.set(newInstance, value);
            }
        } catch (Exception e) {
            log.error("【excel导入】excel实体转换异常！字段【{}】,值（{}）, {}, {}",
                    field.getAnnotation(ExcelImport.class).titleName(), value, newInstance, e);
            throw new ExcelException(7777, "【excel导入】excel实体转换异常！ 字段【"
                    + field.getAnnotation(ExcelImport.class).titleName() + "】，值（" + value + "）");
        }
    }

    /**
     * 实体判空，注解判空
     *
     * @param clazz
     * @return
     */
    private static Field[] getSortFieldsImport(Class clazz) {
        //获取对象总数量
        Field[] fields = clazz.getDeclaredFields();
        if (fields == null || fields.length == 0) {
            log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "实体空异常！");
            throw new ExcelException(BasicCode.BEAN_ERR);
        }
        List<Field> list = new ArrayList<>();

        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelImport.class)) {
                list.add(field);
            }
        }
        fields = new Field[list.size()];
        int num = 0;
        for (Field field : list) {
            fields[num++] = field;
        }
        Arrays.sort(fields, (field, fieldAfter) -> {
            return field.getAnnotation(ExcelImport.class).column()
                    - fieldAfter.getAnnotation(ExcelImport.class).column();
        });
        return fields;
    }

    /**
     * 实体判空，注解判空
     *
     * @param clazz
     * @return
     */
    private static Field[] getSortFieldsExcelExport(Class clazz) {
        //获取对象总数量
        Field[] fields = clazz.getDeclaredFields();
        if (fields == null || fields.length == 0) {
            log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "实体空异常！");
            throw new ExcelException(BasicCode.BEAN_ERR);
        }
        List<Field> list = new ArrayList<>();

        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelExport.class)) {
                list.add(field);
            }
        }
        fields = new Field[list.size()];
        int num = 0;
        for (Field field : list) {
            fields[num++] = field;
        }
        Arrays.sort(fields, (field, fieldAfter) -> {
            return field.getAnnotation(ExcelExport.class).order()
                    - fieldAfter.getAnnotation(ExcelExport.class).order();
        });
        return fields;
    }

    /**
     * 实体判空，注解判空
     *
     * @param clazz
     * @return
     */
    private static Field[] getSortFieldsByExcelSheet(Class clazz) {
        //获取对象总数量
        Field[] fields = clazz.getDeclaredFields();

        if (fields == null || fields.length == 0) {
            log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "实体空异常！");
            throw new ExcelException(BasicCode.BEAN_ERR);
        }
        List<Field> list = new ArrayList<>();

        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelSheet.class)) {
                list.add(field);
            }
        }
        fields = new Field[list.size()];
        int num = 0;
        for (Field field : list) {
            fields[num++] = field;
        }
        Arrays.sort(fields, (field, fieldAfter) -> {
            return field.getAnnotation(ExcelSheet.class).sheetNum()
                    - fieldAfter.getAnnotation(ExcelSheet.class).sheetNum();
        });
        return fields;
    }

    /**
     * 列转化值
     *
     * @param cell 列值
     * @throws IOException
     */
    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        Object cellValue;
        // 把数字当成String来读，避免出现1读成1.0的情况
        // 判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:

                if (DateUtil.isValidExcelDate(cell.getNumericCellValue())) {
                    CellStyle style = cell.getCellStyle();
                    if (style == null) {
                        return false;
                    }
                    int formatIndex = style.getDataFormat();
                    String formatString = style.getDataFormatString();
                    boolean isDate = DateUtil.isADateFormat(formatIndex, formatString);
                    if (isDate) {
                        Date date = cell.getDateCellValue();
                        return date;
                    }
                }
                if ((long) cell.getNumericCellValue() != cell.getNumericCellValue()) {
                    // double 类型
                    cellValue = new BigDecimal(String.valueOf(cell.getNumericCellValue()));

                } else {
                    cellValue = (long)cell.getNumericCellValue();
                }
                break;
            // 字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            // Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 公式
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            // 空值
            case BLANK:
                cellValue = null;
                break;
            // 故障
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 创建工作簿
     *
     * @param type
     * @return
     */
    private static Workbook getWorkbook(ExcelType type) {
        Workbook workbook = null;
        switch (type) {
            case XLS:
                workbook = new HSSFWorkbook();
                break;
            case XLS_X:
                workbook = new XSSFWorkbook();
                break;
            default:
                log.error("【excel导出】{}", "excel类型错误，只支持xls与xlsx！");
                throw new ExcelException(BasicCode.XLS_XLSX_ERR);
        }
        return workbook;
    }

    /**
     * 由文件生成 poi Workbook
     *
     * @param file
     * @return
     */
    private static Workbook getWorkBook(MultipartFile file) {
        // 获得文件名
        String fileName = file.getOriginalFilename();
        // 创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        // 获取excel文件的io流
        InputStream is = null;
        try {
            is = file.getInputStream();
            // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(XLS)) {
                // 2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(XLS_X)) {
                // 2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            throw new ExcelException(BasicCode.EXCEL_TURN_HSSFWORKBOOK_ERR);
        }
        return workbook;
    }

    /**
     * 检查文件
     *
     * @param file
     * @throws IOException
     */
    private static void checkFile(MultipartFile file) {
        // 判断文件是否存在
        if (null == file) {
            throw new ExcelException(BasicCode.FILE_NOT_ERR);
        }
        // 获得文件名
        String fileName = file.getOriginalFilename();
        // 判断文件是否是excel文件
        if (!fileName.endsWith(XLS) && !fileName.endsWith(XLS_X)) {
            log.error(fileName + "不是excel文件");
            throw new ExcelException(BasicCode.NOT_EXCEL_FILE);
        }
    }


    /**
     * 初始化实体
     *
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> Object getNewInstance(Class<T> clazz) {

        try {
            return clazz.newInstance();
        } catch (IllegalAccessException e) {
            log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "excel导入异常！");
            throw new ExcelException(BasicCode.IMPORT_ERR);
        } catch (InstantiationException e) {
            log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "excel导入异常！");
            throw new ExcelException(BasicCode.IMPORT_ERR);
        }
    }


}
